<?php

class mconsignmenttitip extends CI_Model {

    //ambil record pada table t_trx_sell_in berdasarkan id
    public function getById($id) {
        $id = intval($id);

        $query = $this->db->where('sell_in_id', $id)->limit(1)->get('t_trx_sell_in');

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return array();
        }
    }

 

    //ambil record pada table t_mtr_item dan t_mtr_item_group
    public function getItemDetail($id) {
        $listItem = array();
        // ******** Mod by anggwa@gmail.com 20120504 ******** //
        // $this->db->select('item_name, item_code, item_group_id');
        // $this->db->where('iccid', $id);
        // $q = $this->db->get('t_mtr_item');
        // $listItem = $q->result_array();	
        // $this->db->select('item_group_name');
        // $this->db->where('item_group_id', $listItem[0]['item_group_id']);
        // $q = $this->db->get('t_mtr_item_group');
        // $q = $q->result_array();
        // $listItem[0]['item_group_name'] = $q[0]['item_group_name'];

        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        $query = $dbconn->query("SELECT itm.item_name, itm.mdn,  itm.item_code, grp.item_group_id, grp.item_group_name " .
                "FROM t_mtr_item itm " .
                "JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code " .
                "JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id " .
                "WHERE itm.iccid = '$id' LIMIT 1");
        //$query = $dbconn->query($sql);		
        if ($query->num_rows() > 0) {
            $listItem = $query->result_array();
        } else {
            return array();
        }

        return $listItem;
    }


    

    //lakukan insert ke table t_trx_sell_in
    function saveentrusted() {

        $dblokal = $this->load->database("default", true);
        $sell_date = $this->input->post("sell_date");
        $remark = $this->input->post("remark");
        $kon_date = $this->input->post("kon_date");
        $top_id = $this->input->post("term_of_payment");
        
        /* yaddisurahman@gmail.com -- 19 Mei
          sales_id 	=> 92000418 | Apriana
          channel_id 	=> JBL20689 | ABOET CELL
          need to be explode()
         */
        
        $sid = $this->input->post("sales_person");
        $sales_id = explode(" ", $sid);
        $cid = $this->input->post("outlet_name");
        $channel_id = explode(" ", $cid);
        $query=$this->db->query("select f_get_territory_id_by_channel_id('" . $channel_id[0] . "') as id")->row();
        //print_r($query->id);echo "select f_get_territory_id_by_channel_id('" . $channel_id[0] . "') as id";return;
        $data = array(
            "term_id" => $top_id,
            "created_by" => $this->session->userdata('username'),
            "updated_by" => $this->session->userdata('username'),
            "user_id" => $sales_id[0],
            "territory_id" => $query->id,//;//$channel_id[0],
            "consignment_date"=>$kon_date,
            "acess_via" => "web",
            "istatus" => 1,
            "remark"=>$remark
        );
        //print_r($data);return;
        
        $dblokal->insert("t_trx_consignment", $data);
        $last = $this->db->insert_id();

        // 20120514 anggwa@gmail.com -> log
        $this->mglobal->saveLog('consignment', 'create', $last);

        return $last;
    }

  
  
  //save t_trx_consignment_program
  function saveentrustedProgram($lastSid) {
        $this->db->trans_begin();

        $dblokal = $this->load->database("default", true);
        $sell_date = $this->input->post("sell_date");
        $sales_id = $this->input->post("sales_person");

        // $listProgram = array();
        // $listProgram = $this->input->post("program_ho") . $this->input->post("program_cluster");
        // $program_id = explode(",", $listProgram);

        /*
        ** @contoh variable      : 11# Gido SP, 14# Outlet Expansion,
        ** 11                    : ID Program
        ** Gido SP               : Nama Program
        ** 11# Gido SP,          : Program 1
        ** 14# Outlet Expansion, : Program 2
        */



    $pho=$this->input->post("program_ho");
    $pcs=$this->input->post("program_cluster");
    if(strpos($pho,',')==null and strlen($pho)>0){$pho.=',';}
    if(strpos($pcs,',')==null and strlen($pcs)>0){$pcs.=',';}
    
    
        $hoProgTemp1 = explode(",", $pho);
        $clProgTemp1 = explode(",", $pcs);

        for ($i=0; $i < count($hoProgTemp1) ; $i++) { 
            $hoProgTemp[]  = explode("#", $hoProgTemp1[$i]);
            $program_id[]  =  $hoProgTemp[$i][0];
        }
        unset($program_id[$i-1]);
        for ($j=0; $j < count($clProgTemp1); $j++) { 
            $clProgTemp[]  = explode("#", $clProgTemp1[$j]);
            $program_id[]  =  $clProgTemp[$j][0];
        }
        unset($program_id[$i+$j-1]);


        $data = array(
            "consignment_id" => $lastSid,
            "created_by" => $this->session->userdata('username'),
            "updated_by" => $this->session->userdata('username'),
            "created_on" => $sell_date,             
            "updated_on" => $sell_date,             
        );
        print_r($program_id);
        foreach ($program_id as $key) {
            $data["program_id"] = $key;

            $dblokal->insert("t_trx_consignment_program", $data);

            // 20120514 anggwa@gmail.com -> log
            $last = $this->db->insert_id();
            $this->mglobal->saveLog('consignment-program', 'create', '(header='.$lastSid.';detail='.$last.')');
        }
    }

    //lakukan insert ke table t_trx_consignment_detail
    function saveentrustedInDetail($sid, $iccid, $price) {

        $dblokal = $this->load->database("default", true);
        $sell_date = $this->input->post("sell_date");
        $price=str_replace(".","",$price);
        $price=str_replace(",",".",$price);
        $data = array(
            // "iccid" => $iccid[$i], 
            "iccid" => $iccid,
            "consignment_id" => $sid,
            "istatus" => 1,
            "created_by" => $this->session->userdata('username'),
            "created_on" => $sell_date,
            "updated_by" => $this->session->userdata('username'),
            "updated_on" => $sell_date,
            "price"=>$price
        );

        $dblokal->insert("t_trx_consignment_detail", $data);

        // 20120514 anggwa@gmail.com -> log
        $this->mglobal->saveLog('consignment-detail', 'create', '(header=' . $sid . ' detail=' . $iccid .')');

        //update istatus pada table t_trx_scan_in_detail menjadi 5
        $data2 = array(
            'istatus' => 5,
            'transaction_type' => 'Konsinyasi',
            'transaction_id' => $sid
        );
        $this->db->update('t_trx_scan_in_detail', $data2, array('iccid' => $iccid));
    }

   

    function checkUserGroup($sales_id) {
        $this->db->select('grp.user_group_name');
        $this->db->from('t_mtr_user as usr');
        $this->db->join('t_mtr_user_group as grp', 'usr.user_group_id = grp.user_group_id');
        $this->db->where('usr.user_id', $sales_id);
        $q = $this->db->get();

        if ($q->num_rows() > 0) {
            $q = $q->result_array();
            return $q[0]['user_group_name'];
        } else {
            return array();
        }
    }

    function getOutletList($sales_id) {
        $userGroup = $this->checkUserGroup($sales_id);
        $tablePartner = "";
        $curDay = date("j");

        if ($userGroup == "Direct Sales") {
            $curYm = date("YW");

            $tablePartner = "t_mtr_agent";
            $tableKey = "agent_id";
            $partner = "agent";

            if (date("N") == 1)
                $curDay = 7;
            else
                $curDay = date("N");

            // return date("N");
        } else if ($userGroup == "Smart Fren Ambassador") {
            $curYm = date("Ym");
            $tablePartner = "t_mtr_outlet";
            $tableKey = "outlet_id";
            $partner = "outlet";
            $curDay = date("j");
        } else {
            return array();
        }

        // SELECT jc.channel_id, outlet.outlet_name
        // FROM t_jc_88001695 AS jc
        // JOIN t_mtr_outlet AS outlet ON jc.channel_id = outlet.channel_id
        // WHERE jc.d18 = 1 AND jc.yyyymm = '201205'
//        $db = $this->load->database("default", TRUE);
        $query = $this->db->select("jc.channel_id, par." . $partner . "_name AS outlet_name");
        $query = $this->db->from("t_jc_" . $sales_id . " AS jc");
        $query = $this->db->join($tablePartner . " AS par", "jc.channel_id = par." . $tableKey);
        $query = $this->db->where("jc.d" . $curDay, '1');
        $query = $this->db->where("jc.yyyymm", $curYm);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
        }
    }

    

    //ambil daftar iccid dari barang-barang yang diambil sales tadi pagi pada cluster yang bersangkutan, untuk ditampilkan pada textbox autocomplete
    function getRelatedIccid($salesId = "", $term = "") {

        $this->db->distinct();
        $this->db->select('scand.iccid');
        $this->db->from('t_trx_scan_in_detail as scand');
        $this->db->like('scand.iccid', $term, 'after');
        $this->db->join('t_trx_scan_in as scan', 'scan.scan_in_id = scand.scan_in_id');
        $this->db->where('scan.user_id', $salesId);
        $this->db->where('scand.istatus', 1);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return array();
        }
    }

    //get nik name from user
    function getNikName($userID) {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_nik_name('$userID')";
        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_nik_name'];
        } else {
            return array();
        }
    }

    //get item price for each sales
    function getPrice($iccid = "", $userID = "") {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_price('$iccid','$userID')";
        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_price'];
        } else {
            return array();
        }
    }

    public function getSellInProgHo($SellInId) {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_sell_in_program_ho('$SellInId')";

        $query = $dbconn->query($sql);
        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_sell_in_program_ho'];
        } else {
            return array();
        }
    }

    public function getSellInProgCluster($SellInId, $SalesId) {
        //Di Blok dulu biar ga error
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_sell_in_program_cluster('$SellInId','$SalesId')";
        $query = $dbconn->query($sql);
        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_sell_in_program_cluster'];
            return $query->result();
        } else {
            return array();
        }

        return "1";
    }

    public function getPartner($channelId) {
        //Di Blok dulu biar ga error
        $dbconn = $this->load->database("default", TRUE);
        $query = $dbconn->query("SELECT f_get_channel_name('$channelId')");
        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_channel_name'];
            //return $query->result();
        } else {
            return array();
        }
    }
    
    public function termins() {
        $query =$this->db->get_where("t_mtr_consignment_term",array("istatus" => 1));
        return $query->result();
    }
    
    
    
    
    
    
    
       //ambil record pada table t_trx_sell_in berdasarkan id
    public function getDetailProgram($sid, $type, $sales_id) {

        $listProgram = array();
        $listProgram['concate'] = "";
        // $listProgram['list'] = "";
        $jj = 0;

        // $this->db->select('program_id');
        // $this->db->where('sell_in_id', $sid);
        // $q = $this->db->get('t_trx_sell_in_program');      


        if ($type == "ho") {
            $listProgram['concate'] = $this->getSellInProgHo($sid);
            $hoList = explode(", ", $listProgram['concate']);

            if (count($hoList)>0)
                {
                    $listProgram[] = array('program_id' => '1', 'caption' =>'Reguler' );
                }
            else
                {
                foreach ( $hoList as $programHo ) {
                    // echo $programHo. ' ';
                    $query = $this->db->select('program_id, caption');
                    $query = $this->db->where('program_id', $programHo);
                    $query = $this->db->get( 't_mtr_program' );
                    $listProgram[] = $query->result_array();
                }
            }
        } else {
            $listProgram['concate'] = $this->getSellInProgCluster($sid, $sales_id);
            $clList = explode(", ", $listProgram['concate']);
            if (count($clList)>0)
                {
                    $listProgram[] = array('program_id' => '1', 'caption' =>'Reguler' );
                }
            else
                {
                    foreach ( $clList as $programCluster ) {
                    $query = $this->db->select('program_id, caption');
                    $query = $this->db->where('program_id', $programCluster);
                    $query = $this->db->get( 't_mtr_program' );

                    $listProgram[] = $query->result_array();
                    }
                }
        }

        return $listProgram;
        // return $holist. $clList;
    }
    
    function getProgram($sid, $program) {
        $userGroup = $this->checkUserGroup($sid);
        if ($userGroup == "Direct Sales") {
            $pType = 4;
        } else if ($userGroup == "Smart Fren Ambassador") {
            $pType = 3;
        } else {
            return array();
        }

        $userLogin = $this->session->userdata('username');
        $dbconn = $this->load->database("default", TRUE);
        if ($program == "ho") {
            $sql = "select program_id as mvalue, caption as mdisplay
					from t_mtr_program
					where territory_id = 1 and program_type_id = '$pType' and istatus = 1
					order by caption asc";
        } else {
            $sql = "select program_id as mvalue, caption as mdisplay
					from t_mtr_program
					where territory_id = f_get_cluster('$userLogin') and program_type_id = '$pType' and istatus = 1
					order by caption asc";
        }
        $query = $dbconn->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
        }
    }
    public function getAllSellin() {

        $res = array();
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');

        /** @edited Alfin Akhret <alfin.akhret@gmail.com>
        /* optimasi query
        */
        $cluster = $this->db->query("select * from f_get_cluster('".$userlogin."')");
        $cluster = $cluster->result();
        $cluster = $cluster[0]->f_get_cluster;
       
        //mod by anggwa@gmail.com 20120505
//        $sql = "SELECT to_char(created_on, 'DD Mon YYYY') as created_on, sell_in_id, user_id FROM t_trx_sell_in
//				WHERE DATE_PART('day', now() - created_on::timestamp) 
//				BETWEEN 0 AND DATE_PART('day',now() - (now() - interval '1 month'))
//				AND f_get_cluster(user_id) = f_get_cluster('$userlogin')
//				ORDER BY sell_in_id DESC, created_on desc";

        $query = $dbconn->query("SELECT to_char(created_on, 'DD Mon YYYY') as created_on, consignment_id, user_id FROM t_trx_consignment
				WHERE DATE_PART('day', now() - created_on::timestamp) 
				BETWEEN 0 AND DATE_PART('day',now() - (now() - interval '1 month'))
				AND f_get_cluster(user_id) = '$cluster'
				ORDER BY consignment_id DESC, created_on desc");

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $res) {
                $res->getNikName = $this->getNikName($res->user_id);
                //mod by anggwa@gmail.com 20120505
                //$res->tanggalJual = $this->getNikName($res->user_id);
            }
            return $query->result();
        } else {
            return array();
        }
    }



     //ambil nama territory dari id user yang bersangkutan
    public function getClusterName($id = '') {

        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_territory_path(f_get_cluster('$id')) as territory_name, f_get_cluster('$id') as territory_id";
        $query = $dbconn->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return array();
        }
        // $this->db->select('territory_name, territory_id');
        // $this->db->from('t_mtr_territory');
        // $this->db->where('user_id', $id);
        // $this->db->limit(1, 0);
        // $query = $this->db->get();
        // return $query->result_array();
    }

    //ambil daftar sales id yang terdaftar pada territory yang sama dengan user yang sedang login
    public function getSalesID($curLogin = "") {
//        $tes = "cluster";
//
//        $this->db->select('ter.territory_name, ter.territory_id');
//        $this->db->from('t_mtr_territory AS ter');
//        $this->db->join("t_mtr_territory_type AS terty", "ter.territory_type_id = terty.territory_type_id AND lower(terty.territory_type_name)='$tes'");
//        $this->db->where('ter.user_id', $curLogin);
//        $q = $this->db->get();
//        $query = $q->result_array();
//
//        $terri = $query[0]['territory_id'];

        /**
         * anggwa@gmail.com 20120520 -> Menampilkan SFA dan DS yang tidak ada di teritori
         * tadinya yg tampil hanya SFA karena si DS tidak memaintain sebuah agent 
         */
        $this->db->select('usr.user_id, usr.user_name, usr.user_group_id');
        $this->db->group_by('usr.user_id');
        $this->db->from('t_mtr_user AS usr');
        $this->db->join('t_mtr_user_group AS grp', 'usr.user_group_id = grp.user_group_id AND usr.istatus = 1 AND grp.having_stock = 1');
        $this->db->join('t_trx_scan_in AS scan', 'usr.user_id = scan.user_id AND scan.istatus = 1');
        $this->db->where("f_get_cluster(usr.user_id) = f_get_cluster('$curLogin')");
//        $this->db->join('t_mtr_user_territory AS uster', 'usr.user_id = uster.user_id');
//        $this->db->join("t_mtr_territory AS ter", "uster.territory_id = ter.territory_id AND ter.parent_id = '$terri'");
        $this->db->order_by("usr.user_name", "asc");
        $query = $this->db->get();

        //return $query->result();


        if ($query->num_rows() > 0) {
            foreach ($query->result() as $res) {
                $res->getNikName = $this->getNikName($res->user_id);
            }
            return $query->result();
        } else {
            return array();
        }
    }

    // populate latest scaned items
    function getItems($sid) {

        // SELECT itm.iccid, itm.item_code, scan.user_id, itm.mdn, grp.item_group_name, itm.item_name, itm.reseller_price, itm.default_price 
        // FROM t_trx_scan_in_detail AS scand 
        // JOIN t_trx_scan_in AS scan ON scand.scan_in_id = scan.scan_in_id AND scand.istatus = 1 
        // JOIN t_mtr_item AS itm on scand.iccid = itm.iccid
        // JOIN t_mtr_item_group AS grp ON itm.item_group_id = grp.item_group_id
        // WHERE scan.user_id = '$sid'

        $this->db->select("itm.iccid, itm.item_code, scan.user_id, itm.mdn, grp.item_group_name, itm.item_name, itm.reseller_price, itm.default_price");
        $this->db->from("t_trx_scan_in_detail AS scand");
        $this->db->join("t_trx_scan_in AS scan", "scand.scan_in_id = scan.scan_in_id AND scand.istatus = 1");
        $this->db->join("t_mtr_item AS itm", "scand.iccid = itm.iccid");
        $this->db->join("t_mtr_item_group AS grp", "itm.item_group_id = grp.item_group_id");
        $this->db->where("scan.user_id", $sid);
        $this->db->where("itm.iccid not in (select iccid from t_trx_sell_in_detail AS ds join t_trx_sell_in as hs on ds.sell_in_id = hs.sell_in_id)");
        $query = $this->db->get();

        foreach ($query->result() as $res) {
            $iccid = $res->iccid;
            $res->getPrice = $this->getPrice($iccid, $sid);
        }
        return $query->result();
    }

    //ambil record-record yang berkaitan dengan proses pembuatan sell in
    function getSell($iccid) {
        $sales_id = $this->input->post("sales_person");

        $this->db->select('itm.item_code, itm.mdn, grp.item_group_name, itm.item_name, itm.reseller_price, itm.default_price');
        $this->db->from('t_trx_scan_in_detail AS scand');
        $this->db->join("t_trx_scan_in AS scan", "scand.scan_in_id = scan.scan_in_id AND scand.istatus = 1");
        $this->db->join("t_mtr_item AS itm", "scand.iccid = itm.iccid AND itm.iccid = '$iccid'");
        $this->db->join('t_mtr_item_group AS grp', 'itm.item_group_id = grp.item_group_id');
        $query = $this->db->get();
        //return $query->result_array();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $res) {
                $res->getPrice = $this->getPrice($iccid, $sales_id);
            }
            return $query->result();
        } else {
            return array();
        }
    }
}

//end class